﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Web.Security;
using System.Data;
using System.Data.Odbc;
using System.Configuration;
using System.Configuration.Provider;

namespace Components.SQL
{
    public sealed class ErendoraRoleProvider : System.Web.Security.RoleProvider
    {
        private string pApplicationName;
        private string connectionString;
        private string exceptionMessage = "An exception ocurred. Please check the event log.";

        public override string ApplicationName
        {
            get { return pApplicationName; }
            set { pApplicationName = value; }
        }

        public override void Initialize(string name, System.Collections.Specialized.NameValueCollection config)
        {
            if (config == null)
                throw new ArgumentNullException("config");

            if (name == null || name.Length == 0)
                name = "ErendoraRoleProvider";

            base.Initialize(name, config);

            if (config["applicationName"] == null || config["applicationName"].Trim() == "")
            {
                pApplicationName = System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath;
            }
            else
            {
                pApplicationName = config["applicationName"];
            }


            ConnectionStringSettings ConnectionStringSettings = ConfigurationManager.ConnectionStrings[config["connectionStringName"]];

            if (ConnectionStringSettings == null || ConnectionStringSettings.ConnectionString.Trim() == "")
            {
                throw new ProviderException("Connection string cannot be blank");
            }

            connectionString = ConnectionStringSettings.ConnectionString;
        }

        public override void AddUsersToRoles(string[] usernames, string[] roleNames)
        {
            foreach (string username in usernames)
            {
                foreach (string roleName in roleNames)
                {
                    OdbcConnection conn = new OdbcConnection(connectionString);
                    OdbcCommand cmd = new OdbcCommand("{CALL webAddUserToRole (?,?,?)}", conn);
                    
                    cmd.Parameters.Add("@account", OdbcType.VarChar, 32).Value = username;
                    cmd.Parameters.Add("@role", OdbcType.Char,1).Value = roleName;
                    cmd.Parameters.Add("@applicationName", OdbcType.VarChar, 255).Value = pApplicationName;

                    try
                    {
                        conn.Open();

                        cmd.ExecuteNonQuery();
                    }
                    catch (OdbcException e)
                    {
                        throw e;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }

        public override void CreateRole(string roleName)
        {
            OdbcConnection conn = new OdbcConnection(connectionString);
            OdbcCommand cmd = new OdbcCommand("{CALL webCreateRole (?,?)}", conn);

            cmd.Parameters.Add("@role", OdbcType.Char, 1).Value = roleName;
            cmd.Parameters.Add("@applicationName", OdbcType.VarChar, 255).Value = pApplicationName;

            try
            {
                conn.Open();

                cmd.ExecuteNonQuery();
            }
            catch (OdbcException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }

        public override bool DeleteRole(string roleName, bool throwOnPopulatedRole)
        {
            OdbcConnection conn = new OdbcConnection(connectionString);
            OdbcCommand cmd = new OdbcCommand("{? = CALL webDeleteRole (?,?)}", conn);

            OdbcParameter prm = cmd.Parameters.Add("@RETURN_VALUE", OdbcType.Int);
            prm.Direction = ParameterDirection.ReturnValue;

            cmd.Parameters.Add("@role", OdbcType.Char, 1).Value = roleName;
            cmd.Parameters.Add("@applicationName", OdbcType.VarChar, 255).Value = pApplicationName;

            int rowsAffected = 0;
            try
            {
                conn.Open();

                cmd.ExecuteNonQuery();

                rowsAffected = (prm.Value == null ? 0 : Convert.ToInt32(prm.Value));
            }
            catch (OdbcException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return (rowsAffected > 0);
        }

        public override string[] FindUsersInRole(string roleName, string usernameToMatch)
        {
            OdbcConnection conn = new OdbcConnection(connectionString);
            OdbcCommand cmd = new OdbcCommand("{CALL webFindUsersInRole (?,?)}", conn);

            cmd.Parameters.Add("@role", OdbcType.Char, 1).Value = roleName;
            cmd.Parameters.Add("@accountToMatch", OdbcType.VarChar, 32).Value = usernameToMatch;

            OdbcDataReader reader = null;
            string tmpUserNames = "";
            try
            {
                conn.Open();

                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    tmpUserNames += reader.GetString(0) + ",";
                }
            }
            catch (OdbcException e)
            {
                throw e;
            }
            finally
            {
                if (reader != null)
                    reader.Close();
                conn.Close();
            }
            if (tmpUserNames.Length > 0)
            {
                tmpUserNames = tmpUserNames.Substring(0, tmpUserNames.Length - 1);
                return tmpUserNames.Split(',');
            }
            return new string[0];
        }

        public override string[] GetAllRoles()
        {
            OdbcConnection conn = new OdbcConnection(connectionString);
            OdbcCommand cmd = new OdbcCommand("{CALL webGetAllRoles ()}",conn);

            OdbcDataReader reader = null;
            string tmpRoles = "";
            try
            {
                conn.Open();

                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    tmpRoles += reader.GetString(0) + ",";
                }
            }
            catch (OdbcException e)
            {
                throw e;
            }
            finally
            {
                if (reader != null)
                    reader.Close();
                conn.Close();
            }
            if (tmpRoles.Length > 0)
            {
                tmpRoles = tmpRoles.Substring(0, tmpRoles.Length - 1);
                return tmpRoles.Split(',');
            }
            return new string[0];
        }

        public override string[] GetRolesForUser(string username)
        {
            OdbcConnection conn = new OdbcConnection(connectionString);
            OdbcCommand cmd = new OdbcCommand("{CALL webGetRolesForUser (?)}", conn);

            cmd.Parameters.Add("@account", OdbcType.VarChar, 32).Value = username;

            string tmpRoles = "";
            OdbcDataReader reader = null;

            try
            {
                conn.Open();

                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    tmpRoles += reader.GetString(0) + ",";
                }
            }
            catch (OdbcException e)
            {
                throw e;
            }
            finally
            {
                if (reader != null)
                    reader.Close();
                conn.Close();
            }
            if (tmpRoles.Length > 0)
            {
                tmpRoles = tmpRoles.Substring(0, tmpRoles.Length - 1);
                return tmpRoles.Split(',');
            }
            return new string[0];
        }

        public override string[] GetUsersInRole(string roleName)
        {
            OdbcConnection conn = new OdbcConnection(connectionString);
            OdbcCommand cmd = new OdbcCommand("{CALL webGetUsersInRole (?)}", conn);

            cmd.Parameters.Add("@role", OdbcType.Char, 1).Value = roleName;

            string tmpUsers = "";
            OdbcDataReader reader = null;
            try
            {
                conn.Open();

                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    tmpUsers += reader.GetString(0) + ",";
                }
            }
            catch (OdbcException e)
            {
                throw e;
            }
            finally
            {
                if (reader != null)
                    reader.Close();
                conn.Close();
            }
            if (tmpUsers.Length > 0)
            {
                tmpUsers = tmpUsers.Substring(0, tmpUsers.Length - 1);
                return tmpUsers.Split(',');
            }
            return new string[0];
        }

        public override bool IsUserInRole(string username, string roleName)
        {
            OdbcConnection conn = new OdbcConnection(connectionString);
            OdbcCommand cmd = new OdbcCommand("{? = CALL webIsUserInRole (?,?)}", conn);

            OdbcParameter prm = cmd.Parameters.Add("@RETURN_VALUE", OdbcType.Int);
            prm.Direction = ParameterDirection.ReturnValue;

            cmd.Parameters.Add("@account", OdbcType.VarChar, 32).Value = username;
            cmd.Parameters.Add("@role", OdbcType.Char, 1).Value = roleName;

            int Count = 0;
            try
            {
                conn.Open();

                cmd.ExecuteNonQuery();

                Count = (prm.Value == null ? 0 : Convert.ToInt32(prm.Value));
            }
            catch (OdbcException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return (Count > 0);
        }

        public override void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
        {
            foreach (string username in usernames)
            {
                foreach (string roleName in roleNames)
                {
                    OdbcConnection conn = new OdbcConnection(connectionString);
                    OdbcCommand cmd = new OdbcCommand("{CALL webRemoveUserFromRole (?,?)}", conn);

                    cmd.Parameters.Add("@account", OdbcType.VarChar, 32).Value = username;
                    cmd.Parameters.Add("@role", OdbcType.Char, 1).Value = roleName;

                    try
                    {
                        conn.Open();

                        cmd.ExecuteNonQuery();
                    }
                    catch (OdbcException e)
                    {
                        throw e;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }

        public override bool RoleExists(string roleName)
        {
            OdbcConnection conn = new OdbcConnection(connectionString);
            OdbcCommand cmd = new OdbcCommand("SELECT COUNT(*) FROM ROLES_WEB_TBL WHERE role = ? AND applicationName = ?",conn);

            cmd.Parameters.Add("@role", OdbcType.Char, 1).Value = roleName;
            cmd.Parameters.Add("@applicationName", OdbcType.VarChar, 255).Value = pApplicationName;

            int Count = 0;
            try
            {
                conn.Open();

                Count = (int)cmd.ExecuteScalar();
            }
            catch (OdbcException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return (Count > 0);
        }
    }
}
